﻿/*
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Validation;

namespace SearchInFiles.Lib.OpenFile
{
    public class OpenXmlXlsxHelper
    {

        /// <summary>
        /// 读取Excel数据到DataSet中，默认读取所有Sheet中的数据
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="m_sheetNames">Sheet名称列表，默认为null查询所有Sheet中的数据</param>
        /// <returns></returns>
        public static DataSet ReadExcel(string filePath, params string[] sheetNames)
        {
            HashSet<string> m_sheetNames = new(sheetNames);
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, true))
            {
                IEnumerable<Sheet> sheets = doc.WorkbookPart?.Workbook.Descendants<Sheet>() ?? new Sheet[0];
                if (m_sheetNames.Count > 0)
                    sheets = sheets.Where(s => (s.Name?.HasValue ?? false) && m_sheetNames.Contains(s.Name!));

                DataSet ds = new DataSet();
                DataTable dt;
                SharedStringTable? stringTable = doc.WorkbookPart?.SharedStringTablePart?.SharedStringTable;
                foreach (Sheet sheet in sheets)
                {
                    if (!sheet.Id?.HasValue ?? true)
                        continue;
                    dt = new DataTable(sheet.Name);
                    foreach (Row row in (((WorksheetPart?)doc.WorkbookPart?.GetPartById(sheet.Id!))?.Worksheet.Descendants<Row>() ?? new Row[0]))
                    {
                        if ((row.RowIndex?.HasValue ?? false) && row.RowIndex == 1)
                        {
                            GetDataColumn(row, stringTable, dt);
                        }
                        GetDataRow(row, stringTable, dt);
                    }
                    ds.Tables.Add(dt);
                }
                return ds;
            }
        }

        /// <summary>
        /// 把Spreadsheet行中的数据导出到DataTable中。
        /// </summary>
        /// <param name="row">Spreadsheet行</param>
        /// <param name="stringTable">共享字符串表</param>
        /// <param name="dt">DataTable</param>
        private static void GetDataRow(Row row, SharedStringTable? stringTable, DataTable dt)
        {
            string cellValue;
            List<string> c = new List<string>();
            foreach (Cell cell in row)
            {
                cellValue = GetCellValue(cell, stringTable);
                if (!string.IsNullOrEmpty(cellValue))
                    c.Add(cellValue);
                c.Add(string.Empty);
            }
            //如果一整行数据都没有数据，则不添加此行到DataTable中
            if (c.Count>0)
                dt.Rows.Add(c);
        }

        /// <summary>
        /// 从Spreadsheet行中读取表头信息
        /// </summary>
        /// <param name="row">Spreadsheet行</param>
        /// <param name="stringTable">共享字符串表</param>
        /// <param name="dt">DataTable</param>
        private static void GetDataColumn(Row row, SharedStringTable? stringTable, DataTable dt)
        {
            DataColumn col = new DataColumn();
            Dictionary<string, int> columnCountDict = new Dictionary<string, int>();
            foreach (Cell cell in row)
            {
                string cellValue = GetCellValue(cell, stringTable);
                col = new DataColumn(cellValue);
                //由于Excel中的数据表列标题可以重复，而DataTable中不允许重复，因此在重复的列标题后追加递增数字
                if (dt.Columns.Contains(cellValue))
                {
                    if (!columnCountDict.ContainsKey(cellValue))
                    {
                        columnCountDict.Add(cellValue, 0);
                    }
                    col.ColumnName = cellValue + (columnCountDict[cellValue]++);
                }
                dt.Columns.Add(col);
            }
        }

        /// <summary>
        /// 获取Spreadsheet单元格的值
        /// </summary>
        /// <param name="cell">Spreadsheet单元格</param>
        /// <param name="stringTable">共享字符串表</param>
        /// <returns>Spreadsheet单元格的值</returns>
        private static string GetCellValue(Cell cell, SharedStringTable? stringTable)
        {
            string value = string.Empty;
            if (cell.ChildElements.Count == 0)
                return value;
            
            value = cell.CellValue?.InnerText ?? string.Empty;
            if (stringTable != null && cell.DataType != null && cell.DataType == CellValues.SharedString && int.TryParse(value, out int result))
                value = stringTable.ChildElements[result].InnerText;
            return value;
        }
    }
}
*/